package com.LostAndFound.dao;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.LostAndFound.domain.Article;
import com.LostAndFound.utils.DataSourceUtils;

public class SearchForLoseDao {

	/* 得到寻物启事的记录数
	 * whatflag = 1 表示寻物启事
	 * foundflag = 1 表示未找到
	 * invalidflag = 1 表示未失效
	 */
	public int getTotalRecordForEasyUI(String cid) throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select count(*) from article "
				+ " where whatflag = 1 and foundflag = 1 and invalidflag = 1 ";
		Long total = 0L;
		if(cid.equals("all")) {
			sql = sql + "";
			total = (Long) runner.query(sql, new ScalarHandler());
		}else {
			sql = sql + " and cid = ?";
			total = (Long) runner.query(sql, new ScalarHandler(),cid);
		}
		return total.intValue();
	}
	// 得到要进行审核的记录总数
	public int getTotalCheckRecordForEasyUI(String cid) throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select count(*) from article "
				+ " where whatflag = 1 and foundflag = 2 and invalidflag = 2 ";
		Long total = 0L;
		if(cid.equals("all")) {
			sql = sql + "";
			total = (Long) runner.query(sql, new ScalarHandler());
		}else {
			sql = sql + " and cid = ?";
			total = (Long) runner.query(sql, new ScalarHandler(),cid);
		}
		return total.intValue();
	}
	// 得到寻物启事的记录
	public List<Map<String, Object>> getRecodListForEasyUI(int startIndex, int pageSize, String cid) throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select a.*, u.*, c.cname from article a,user u,category c "
				+ " where whatflag = 1 and foundflag = 1 and invalidflag = 1 "
				+ " and a.uid = u.uid and a.cid = c.cid ";
		List<Map<String, Object>> mapList = null;
		if(cid.equals("all")) {
			sql = sql + " order by a.issuetime desc limit ?,?";	
			mapList = runner.query(sql, new MapListHandler(),startIndex,pageSize);
		}else{
			sql = sql + " and a.cid = ? order by a.issuetime desc limit ?,?";
			mapList = runner.query(sql, new MapListHandler(),cid,startIndex,pageSize);
		}
		
		return mapList;
	}
	// 得到要进行审核的全部记录
	public List<Map<String, Object>> getCheckRecodListForEasyUI(int startIndex, int pageSize, String cid) throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select a.*, u.*, c.cname from article a,user u,category c "
				+ " where whatflag = 1 and foundflag = 2 and invalidflag = 2 "
				+ " and a.uid = u.uid and a.cid = c.cid ";
		List<Map<String, Object>> mapList = null;
		if(cid.equals("all")) {
			sql = sql + " order by a.issuetime desc limit ?,?";	
			mapList = runner.query(sql, new MapListHandler(),startIndex,pageSize);
		}else{
			sql = sql + " and a.cid = ? order by a.issuetime desc limit ?,?";
			mapList = runner.query(sql, new MapListHandler(),cid,startIndex,pageSize);
		}
		
		return mapList;
	}
	
	public void handleDeleteSearchLoseRecordForEasyUI(String aid) throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "delete from article where aid = ?";
		runner.update(sql, aid);
	}
	/*
	 * 隐藏此功能，2019/03/03
	 * 保存到article
	 */
	public void handleAddNewSearchLose(Article article) throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "insert into article values(?,?,?,?,?,?,?,?,?,?,?)";
		Object[] params = {article.getAid(),article.getTitle(),article.getDetailInfo(),
				article.getIssueTime(),article.getImage(),article.getPhone(),
				article.getWhatFlag(),article.getFoundFlag(),article.getInvalidFlag(),
				article.getCategory().getCid(),article.getUser().getUid()};
		runner.update(sql, params);
	}
	
}
